﻿using System;
using System.Data;
using System.Configuration;
using System.Collections.Generic;
using System.Data.SQLite;
using Common;

namespace BusDiary.Access
{
    public partial class DiaryCatalog
    {
        static object addLock = new object();

        string insertSQL = "insert into DiaryCatalog([DayType],[Title])  values(@DayType,@Title)";
        /// <summary>
        /// 添加一条新的记录
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(BusDiary.Entity.DiaryCatalog model)
        {
            SQLiteParameter[] parameters = new SQLiteParameter[]{
                new SQLiteParameter("@DayType", model.DayType),
                new SQLiteParameter("@Title", model.Title),};
            lock (addLock)
            {
                int row = SQLiteHelper.ExecuteNonQuery(insertSQL, parameters);

                DataSet ds = SQLiteHelper.ExecuteDataset("select max(id) from DiaryCatalog");
                int id = (int)(Int64)ds.Tables[0].Rows[0][0];
                model.ID = id;
            }

            return model.ID;
        }

        string updateSQL = "update DiaryCatalog Set [DayType]=@DayType,[Title]=@Title where [ID]=@ID";        /// <summary>
        /// 更新一个对象
        /// </summary>
        /// <param name="model"></param>
        public void Update(BusDiary.Entity.DiaryCatalog model)
        {
            SQLiteParameter[] parameters = new SQLiteParameter[]{
                new SQLiteParameter("@ID", model.ID),
                new SQLiteParameter("@DayType", model.DayType),
                new SQLiteParameter("@Title", model.Title),};
            SQLiteHelper.ExecuteNonQuery(updateSQL, parameters);
        }

        string deleteSQL = "delete from DiaryCatalog where [ID]=@ID";

        /// <summary>
        /// 删除一个对象
        /// </summary>
        /// <param name="model"></param>
        public void Delete(BusDiary.Entity.DiaryCatalog model)
        {
            Delete(model.ID);
        }

        /// <summary>
        /// 通过关键字段ID删除一条记录
        /// </summary>
        /// <param name="ID"></param>
        public void Delete(int ID)
        {
            SQLiteParameter[] parameters = new SQLiteParameter[]{
                new SQLiteParameter("@ID", ID)};

            SQLiteHelper.ExecuteNonQuery(deleteSQL, parameters);

        }

        string selectSQL = "select * from DiaryCatalog";

        /// <summary>
        /// 根据关键字段ID获得一条数据记录
        /// 如果不存在记录则返回NULL
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public BusDiary.Entity.DiaryCatalog GetByID(int ID)
        {
            SQLiteParameter[] parameters = new SQLiteParameter[]{
                new SQLiteParameter("@ID", ID)};

            string sql = selectSQL + " where ID=@ID";

            IDataReader reader = SQLiteHelper.ExecuteReader(sql, parameters);

            return GetSingle(reader);
        }

        /// <summary>
        /// 从返回的记录里获得第一条数据
        /// 如果没有数据，则返回NULL
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        private static BusDiary.Entity.DiaryCatalog GetSingle(IDataReader reader)
        {
            BusDiary.Entity.DiaryCatalog ret = null;

            try
            {
                if (reader.Read())
                {
                    ret = GetModel(reader);
                }
            }
            finally
            {
                reader.Close();
            }

            return ret;
        }

        /// <summary>
        /// 获得数据库表里所有数据
        /// </summary>
        /// <returns></returns>
        public List<BusDiary.Entity.DiaryCatalog> GetAll()
        {
            IDataReader reader = SQLiteHelper.ExecuteReader(selectSQL);

            return GetMultiEntity(reader);        
        }

        /// <summary>
        /// 从DataReader里获得多条记录
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        private static List<BusDiary.Entity.DiaryCatalog> GetMultiEntity(IDataReader reader)
        {
            List<BusDiary.Entity.DiaryCatalog> ret = new List<BusDiary.Entity.DiaryCatalog>();

            try
            {
                while (reader.Read())
                {
                    ret.Add(GetModel(reader));
                }
            }
            finally
            {
                reader.Close();
            }

            return ret;
        }

        private static BusDiary.Entity.DiaryCatalog GetModel(IDataReader reader)
        {
            BusDiary.Entity.DiaryCatalog ret = new BusDiary.Entity.DiaryCatalog();

            ret.ID = (int)(Int64)reader["ID"];
            ret.DayType = (int)(Int64)reader["DayType"];
            ret.Title = (string)reader["Title"];

            return ret;
        }
        static DiaryCatalog instance = null;

        public static DiaryCatalog Instance
        {
            get
            {
                if (instance == null)
                    instance = new DiaryCatalog();
                return DiaryCatalog.instance;
            }
        }

    }
}

